﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Collections;


namespace EquipmentConfig.Class
{
    public class ExcelOledb
    {

        /// <summary>  
        ///获取2003 所有sheet的名称List
        /// </summary>  
        /// <param name="FName">Name of the F.</param>  
        /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>  
        /// <returns></returns>  
        public static List<string> GetAllSheetName2003(string FName, bool HasFieldName)
        {

            string strConn;
            List<string> sTBList = new List<string>();
            if (HasFieldName)
            {
                /*如果Excel中的第一列为栏位名,则写成*/
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            }
            else
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
            }
            OleDbConnection odc = new OleDbConnection(strConn);
            odc.Open();
            DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    sTBList.Add(dr["TABLE_NAME"].ToString().Replace("$", string.Empty));
                }
            }
            return sTBList;
        }

        /// <summary>  
        /// 获取2007 所有sheet的名称List
        /// </summary>  
        /// <param name="FName">Name of the F.</param>  
        /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>  
        /// <returns></returns>  
        public static List<string> GetAllSheetName2007(string FName, bool HasFieldName)
        {

            string strConn;
            List<string> sTBList = new List<string>();
            if (HasFieldName)
            {
                strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FName + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
            }
            else
            {
                strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FName + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'";
            }
            OleDbConnection odc = new OleDbConnection(strConn);
            odc.Open();
            DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    sTBList.Add(dr["TABLE_NAME"].ToString().Replace("$", string.Empty));
                }
            }
            return sTBList;
        }

        /// <summary>  
        ///获取Excel2003 第一个Sheet名称
        /// </summary>  
        /// <param name="FName">Name of the F.</param>  
        /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>  
        /// <returns></returns>  
        public static string GetFirstSheetName2003(string FName, bool HasFieldName)
        {
            string strConn;
            List<string> sTBList = new List<string>();
            if (HasFieldName)
            {
                /*如果Excel中的第一列为栏位名,则写成*/
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            }
            else
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
            }
            OleDbConnection odc = new OleDbConnection(strConn);
            odc.Open();
            DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    sTBList.Add(dr["TABLE_NAME"].ToString().Replace("$", string.Empty));
                }
            }
            return sTBList[0];
        }

        /// <summary>  
        /// 获取Excel2007 第一個Sheet名稱
        /// </summary>  
        /// <param name="FName">Name of the F.</param>  
        /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>  
        /// <returns></returns>  
        public static string GetFirstSheetName2007(string FName, bool HasFieldName)
        {
            string strConn;
            List<string> sTBList = new List<string>();
            if (HasFieldName)
            {
                /*如果Excel中的第一列为栏位名,则写成*/
                strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FName + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
            }
            else
            {
                strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FName + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'";
            }
            OleDbConnection odc = new OleDbConnection(strConn);
            odc.Open();
            DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    sTBList.Add(dr["TABLE_NAME"].ToString().Replace("$", string.Empty));
                }
            }
            return sTBList[0];
        }

        /// <summary>  
        /// 读取2003 Excel內容
        /// </summary>  
        /// <param name="FName">Name of the F.</param>  
        /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>  
        /// <returns></returns>  
        public static System.Data.DataTable ImportExcel2003(string FName, bool HasFieldName)
        {
            try
            {
                bool b = false;
                List<string> sTBList = new List<string>();
                string strConn;
                /*  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FName +";Extended Properties=Excel 8.0;";*/
                if (HasFieldName)
                {
                    /*如果Excel中的第一列为栏位名,则写成*/
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=2'";
                    b = true;
                }
                else
                {
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=2'";
                }

                OleDbConnection odc = new OleDbConnection(strConn);
                odc.Open();
                DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        sTBList.Add(dr["TABLE_NAME"].ToString());
                    }
                }
                OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + sTBList[0] + "]", strConn);
                System.Data.DataTable myDataSet = new System.Data.DataTable();
                myCommand.Fill(myDataSet);
                odc.Close();
                if (b)
                {
                    myDataSet.Rows.RemoveAt(0);
                }
                return myDataSet;
            }
            catch (Exception e)
            {
                throw e;
            }

        }

        /// <summary>  
        /// 读取2003 Excel內容
        /// </summary>  
        /// <param name="FName">Name of the F.</param>  
        /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>  
        /// <returns></returns>  
        public static List<DataTable> ImportExcel2003Datas(string FName, bool HasFieldName)
        {
            try
            {
                List<DataTable> list = new List<DataTable>();
                bool b = false;
                string strConn;
                /*  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FName +";Extended Properties=Excel 8.0;";*/
                if (HasFieldName)
                {
                    /*如果Excel中的第一列为栏位名,则写成*/
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=2'";
                    b = true;
                }
                else
                {
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=2'";
                }

                OleDbConnection odc = new OleDbConnection(strConn);
                odc.Open();
                DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + dr["TABLE_NAME"].ToString() + "]", strConn);
                        System.Data.DataTable myDataSet = new System.Data.DataTable();
                        myCommand.Fill(myDataSet);
                        if (b)
                        {
                            myDataSet.Rows.RemoveAt(0);
                        }
                        list.Add(myDataSet);
                    }
                }
                return list;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>  
        ///读取2007 Excel內容
        /// </summary>  
        /// <param name="FName">Name of the F.</param>  
        /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>  
        /// <returns></returns>  
        public static System.Data.DataTable ImportExcel2007(string FName, bool HasFieldName)
        {
            try
            {
                bool b = false;
                List<string> sTBList = new List<string>();
                string strConn;
                /*  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FName +";Extended Properties=Excel 8.0;";*/
                if (HasFieldName)
                {
                    /*如果Excel中的第一列为栏位名,则写成*/
                    strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FName + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1;'";
                    b = true;
                }
                else
                {
                    strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FName + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1;'";
                }

                OleDbConnection odc = new OleDbConnection(strConn);
                odc.Open();
                DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        sTBList.Add(dr["TABLE_NAME"].ToString());
                    }
                }
                OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + sTBList[0] + "]", strConn);
                System.Data.DataTable myDataSet = new System.Data.DataTable();
                myCommand.Fill(myDataSet);
                odc.Close();
                if (b)
                {
                    myDataSet.Rows.RemoveAt(0);
                }
                return myDataSet;
            }
            catch (Exception e)
            {
                throw e;
            }

        }

        /// <summary>  
        /// 读取2007 Excel內容
        /// </summary>  
        /// <param name="FName">Name of the F.</param>  
        /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>  
        /// <returns></returns>  
        public static List<DataTable> ImportExcel2007Datas(string FName, bool HasFieldName)
        {
            try
            {
                List<DataTable> list = new List<DataTable>();
                bool b = false;
                string strConn;
                /*  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FName +";Extended Properties=Excel 8.0;";*/
                if (HasFieldName)
                {
                    /*如果Excel中的第一列为栏位名,则写成*/
                    strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FName + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1;'";
                    b = true;
                }
                else
                {
                    strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FName + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1;'";
                }

                OleDbConnection odc = new OleDbConnection(strConn);
                odc.Open();

                DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + dr["TABLE_NAME"].ToString() + "]", strConn);
                        System.Data.DataTable myDataSet = new System.Data.DataTable();
                        myCommand.Fill(myDataSet);
                        if (b)
                        {
                            myDataSet.Rows.RemoveAt(0);
                        }
                        list.Add(myDataSet);
                    }
                }
                return list;
            }
            catch (Exception e)
            {
                throw e;
            }

        }

        /// <summary>  
        /// 写入DataTable 至 2003 
        /// </summary>  
        /// <param name="FName">Name of the F.</param>  
        /// <param name="SheetName">Name of the sheet.</param>  
        /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>  
        /// <param name="InsertData">The insert data.</param>  
        public static void InsertDataTableToExcelSheet2003(string FName, string SheetName, bool HasFieldName, DataTable dtable)
        {
            string strConn;
            if (HasFieldName)
            {
                /*如果Excel中的第一列为栏位名,则写成*/
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            }
            else
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
            }

            string InsertString = "'1','2'";
            //用OldDb方式去建table   
            using (OleDbConnection cn = new OleDbConnection(strConn))
            {
                cn.Open();
                string qs = "INSERT INTO [" + SheetName + "$](A1,B1) VALUES( " + InsertString + " )";
                try
                {
                    using (OleDbCommand cmd = new OleDbCommand(qs, cn))
                    {
                        cmd.CommandText = "INSERT INTO [" + SheetName + "$](A1,B1) VALUES( " + InsertString + " )";
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }


        /// <summary>  
        /// 写入DataTable 至 2007 
        /// </summary>  
        /// <param name="FName">Name of the F.</param>  
        /// <param name="SheetName">Name of the sheet.</param>  
        /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>  
        /// <param name="InsertData">The insert data.</param>  
        public static void InsertDataTableToExcelSheet2007(string FName, string SheetName, bool HasFieldName, DataTable dtable)
        {
            string strConn;
            if (HasFieldName)
            {
                /*如果Excel中的第一列为栏位名,则写成*/
                strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FName + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=0'";
            }
            else
            {
                strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FName + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=0'";
            }


            // 用OldDb方式去建table   
            using (OleDbConnection cn = new OleDbConnection(strConn))
            {
                string InsertString = "";

                string strColumnName = "";
                cn.Open();
                //首先拼接ColumnName
                for (int icol = 0; icol < dtable.Columns.Count; icol++)
                {
                    strColumnName = strColumnName + "," + dtable.Columns[icol].ColumnName;
                }
                strColumnName = strColumnName.TrimEnd(new char[] { ',' }).TrimStart(new char[] { ',' });
                try
                {
                    using (OleDbCommand cmd = new OleDbCommand())
                    {
                        cmd.Connection = cn;

                        foreach (DataRow dr in dtable.Rows)
                        {
                            for (int icol = 0; icol < dtable.Columns.Count; icol++)
                            {
                                InsertString = InsertString + ",'" + dr[icol].ToString() + "'";
                            }
                            InsertString = InsertString.TrimEnd(new char[] { ',' }).TrimStart(new char[] { ',' });
                            cmd.CommandText = "INSERT INTO [" + SheetName + "$](A1,B1,C1,D1,E1) VALUES( " + InsertString + " )";
                            cmd.ExecuteNonQuery();
                        }


                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }


        /// <summary>
        ///  寫入DataTable 至 2007 
        /// </summary>
        /// <param name="FName"></param>
        /// <param name="SheetName"></param>
        /// <param name="HasFieldName"></param>
        /// <param name="dtable"></param>
        public static void InsertDataTableToExcelSheet(string FName, string SheetName, bool HasFieldName, DataTable dtable, bool Is2003)
        {
            string strConn;
            if (HasFieldName)
            {
                if (!Is2003)
                {
                    /*如果Excel中的第一列為欄名,則寫成*/
                    strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FName + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=0'";
                }
                else
                {
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=2'";

                }
            }
            else
            {
                if (Is2003)
                {
                    strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FName + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=0'";
                }
                else
                {
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";

                }
            }

            dtable.TableName = SheetName;
            int iRows = dtable.Rows.Count;
            int iCols = dtable.Columns.Count;

            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("CREATE TABLE ");
            stringBuilder.Append(dtable.TableName + " ( ");
            for (int i = 0; i < iCols; i++)
            {
                //此处是本版本改进中最实用的地方   
                string strType = GetOleDataType(dtable.Columns[i]);
                if (i < iCols - 1)
                    stringBuilder.Append(string.Format("{0} {1},", dtable.Columns[i].ColumnName, strType));
                else
                    stringBuilder.Append(string.Format("{0} {1})", dtable.Columns[i].ColumnName, strType));
            }

            //插入新表 
            using (OleDbConnection cn = new OleDbConnection(strConn))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = cn;
                    cmd.CommandText = stringBuilder.ToString();

                    try
                    {

                        cn.Open();
                        //插入新表   
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }

                    stringBuilder.Remove(0, stringBuilder.Length);
                    stringBuilder.Append("INSERT INTO ");
                    stringBuilder.Append(dtable.TableName + " ( ");

                    for (int i = 0; i < iCols; i++)
                    {
                        if (i < iCols - 1)
                            stringBuilder.Append(dtable.Columns[i].ColumnName + ",");
                        else
                            stringBuilder.Append(dtable.Columns[i].ColumnName + ") values (");
                    }

                    for (int i = 0; i < iCols; i++)
                    {
                        if (i < iCols - 1)
                            stringBuilder.Append("@" + dtable.Columns[i].ColumnName + ",");
                        else
                            stringBuilder.Append("@" + dtable.Columns[i].ColumnName + ")");
                    }
                    //建立插入动作的Command   
                    cmd.CommandText = stringBuilder.ToString();
                    OleDbParameterCollection oleParam = cmd.Parameters;

                    oleParam.Clear();
                    for (int i = 0; i < iCols; i++)
                    {
                        OleDbType oleDbType = GetRefOleDataType(dtable.Columns[i]);

                        //此处是本版本改进中最实用的地方   
                        oleParam.Add(new OleDbParameter("@" + dtable.Columns[i].ColumnName, oleDbType));
                    }

                    //遍历DataTable将数据插入新建的Excel文件中   
                    foreach (DataRow row in dtable.Rows)
                    {
                        for (int i = 0; i < oleParam.Count; i++)
                        {
                            oleParam[i].Value = row[i];
                        }
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }


        public static String GetOleDataType(DataColumn dCol)
        {
            String strDataType = "";

            if (dCol.DataType.Equals(typeof(int)))
            {
                strDataType = "int";
            }
            else if (dCol.DataType.Equals(typeof(string)))
            {
                strDataType = "varchar(20)";
            }
            else if (dCol.DataType.Equals(typeof(Decimal)))
            {

                strDataType = "Decimal";
            }
            else if (dCol.DataType.Equals(typeof(DateTime)))
            {
                strDataType = "Date";
            }
            return strDataType;

        }


        public static OleDbType GetRefOleDataType(DataColumn dCol)
        {
            OleDbType oType = new OleDbType();

            if (dCol.DataType.Equals(typeof(int)))
            {
                oType = OleDbType.Integer;
            }
            else if (dCol.DataType.Equals(typeof(string)))
            {
                oType = OleDbType.VarChar;
            }
            else if (dCol.DataType.Equals(typeof(Decimal)))
            {
                oType = OleDbType.Decimal;
            }
            else if (dCol.DataType.Equals(typeof(DateTime)))
            {
                oType = OleDbType.Date;
            }
            return oType;
        }
    }
}
